﻿//#define DEBUG_SQL
using System;
using System.Data;
using System.Data.SqlServerCe;

namespace CSW.Framework.Common.Data
{
    /// <summary>
    /// Data Helper.
    /// </summary>
    public static class SqlCeDataHelper
    {
        /// <summary>
        /// Gets a <see cref="DataTable"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <returns>The <see cref="DataTable"/>.</returns>
        public static DataTable GetDataTable(SqlCeConnection connection, string sql)
        {
            return GetDataTable(connection, sql, null);
        }

        /// <summary>
        /// Gets a <see cref="DataTable"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlCeParameter"/> array.</param>
        /// <returns>The <see cref="DataTable"/>.</returns>
        public static DataTable GetDataTable(SqlCeConnection connection, string sql, SqlCeParameter[] p)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

#if DEBUG_SQL
            Debug.WriteLine("");
            Debug.WriteLine(sql);
            Debug.WriteLine("");
#endif

            if (connection.State != ConnectionState.Open)
                connection.Open();

            // TODO: Hack for SqlCe not allowing more than one statement
            if (sql.Contains("SELECT @@IDENTITY;"))
            {
                sql = sql.Replace("SELECT @@IDENTITY;", "");

                using (SqlCeCommand cmd = new SqlCeCommand(sql, connection))
                {
                    if (connection.ConnectionTimeout > 30)
                        cmd.CommandTimeout = connection.ConnectionTimeout;
                    if (p != null)
                        cmd.Parameters.AddRange(p);
                    int count = cmd.ExecuteNonQuery();
                    if (count != 1)
                        throw new Exception(string.Format("{0} rows modified; expected 1.", count));
                }

                using (SqlCeCommand cmd = new SqlCeCommand("SELECT @@IDENTITY;", connection))
                using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                {
                    if (connection.ConnectionTimeout > 30)
                        cmd.CommandTimeout = connection.ConnectionTimeout;

                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
            else
            {
                using (SqlCeCommand cmd = new SqlCeCommand(sql, connection))
                using (SqlCeDataAdapter da = new SqlCeDataAdapter(cmd))
                {
                    if (connection.ConnectionTimeout > 30)
                        cmd.CommandTimeout = connection.ConnectionTimeout;
                    if (p != null)
                        cmd.Parameters.AddRange(p);
                    DataSet ds = new DataSet();
                    da.Fill(ds);

                    return ds.Tables[0];
                }
            }
        }

        /// <summary>
        /// Gets a <see cref="DataRow"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <returns>The <see cref="DataRow"/>.</returns>
        public static DataRow GetDataRow(SqlCeConnection connection, string sql)
        {
            return GetDataRow(connection, sql, null);
        }

        /// <summary>
        /// Gets a <see cref="DataRow"/>.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlCeParameter"/> array.</param>
        /// <returns>The <see cref="DataRow"/>.</returns>
        public static DataRow GetDataRow(SqlCeConnection connection, string sql, SqlCeParameter[] p)
        {
            DataTable dt = GetDataTable(connection, sql, p);
            if (dt.Rows.Count != 1)
                throw new Exception(string.Format("Query returned {0} rows, expected 1.", dt.Rows.Count));

            return dt.Rows[0];
        }

        /// <summary>
        /// Executes a SQL statement.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        public static void ExecuteSQL(SqlCeConnection connection, string sql)
        {
            ExecuteSQL(connection, sql, null);
        }

        /// <summary>
        /// Executes a SQL statement.
        /// </summary>
        /// <param name="connection">The connection.</param>
        /// <param name="sql">The SQL statement.</param>
        /// <param name="p">The <see cref="SqlCeParameter"/> array.</param>
        /// <returns>The number of rows affected.</returns>
        public static int ExecuteSQL(SqlCeConnection connection, string sql, SqlCeParameter[] p)
        {
            Guard.ArgumentNotNull(connection, "connection");
            Guard.ArgumentNotNullOrEmptyString(sql, "sql");

#if DEBUG_SQL
            Debug.WriteLine("");
            Debug.WriteLine(sql);
            Debug.WriteLine("");
#endif

            if (connection.State != ConnectionState.Open)
                connection.Open();

            using (SqlCeCommand cmd = new SqlCeCommand(sql, connection))
            {
                if (connection.ConnectionTimeout > 30)
                    cmd.CommandTimeout = connection.ConnectionTimeout;
                if (p != null)
                    cmd.Parameters.AddRange(p);
                int rowCount = cmd.ExecuteNonQuery();

                return rowCount;
            }
        }
    }
}